import sqlite3
from zhixuewang import *

db_file='scores.db'#数据库名，不是表名

def create_table(tablename):
    conn=sqlite3.connect(db_file)
    sql='create table "' + tablename + \
        '"("id" INTEGER NOT NULL UNIQUE,"school" TEXT NOT NULL,"examname" TEXT NOT NULL,' \
        '"name" TEXT NOT NULL,"subject" TEXT NOT NULL,"score" INTEGER NOT NULL,"grade_rank" INTEGER NOT NULL,' \
        '"class_rank" INTEGER NOT NULL,"averagescore" INTEGER NOT NULL,PRIMARY KEY("id" AUTOINCREMENT))'
    cur=conn.cursor()
    cur.execute(sql)
    conn.commit()
    cur.close()
    conn.close()

def get_alldata():
    conn=sqlite3.connect(db_file)
    sql='select * from score'
    cur=conn.cursor()
    cur.execute(sql)
    data=cur.fetchall()
    cur.close()
    conn.close()
    return data 

def insert_multi_data(score_list,table):
    conn=sqlite3.connect(db_file)
    cur=conn.cursor()
    sql='insert into "'+table+ '"(school,examname,name,subject,score,grade_rank,class_rank,averagescore) values(?,?,?,?,?,?,?,?)'
    cur.executemany(sql,score_list)
    conn.commit()
    cur.close()
    conn.close()
    return cur.rowcount

def save_user(name,school,clazz,userid,userpwd):
    db_file = 'scores.db'
    data=(name,school,clazz,userid,userpwd)
    conn = sqlite3.connect(db_file)
    sql = 'insert into user (name,school,class,userid,userpwd) values(?,?,?,?,?)'
    cur = conn.cursor()
    print(data)
    cur.execute(sql, data)
    conn.commit()
    cur.close()
    conn.close()

def get_score_byname(name,table):
    conn=sqlite3.connect(db_file)
    sql='select * from "'+table+'" where name= "'+name+'" order by id'
    cur=conn.cursor()
    cur.execute(sql)
    data=cur.fetchall()
    cur.close()
    conn.close()
    return data 

def get_data_zhixue(examid):
    teacher = login("*********","*********")
    s=teacher.get_scores(examid)
    extra=teacher.get_exam_extra_data(s)
    info=teacher.get_exam_detail(examid)

    avg=[]
    for i in extra:
        sub_avg=i.exam_extra_data.avg_score
        avg.append(sub_avg)

    datalist=[]#(info.name,s[0][0].person.clazz.school.name,"a","a",0,0,0,0)学校名，考试名
    for personscore in s:
        cnt=0
        for sub_score in personscore:
            avgsub = avg[cnt]
            a=(s[0][0].person.clazz.school.name,info.name,sub_score.person.name,sub_score.subject.name,sub_score.score,sub_score.grade_rank,sub_score.class_rank,avgsub)
            datalist.append(a)
            cnt=cnt+1
            """
            print(sub_score.subject.name)
            print("item.score",sub_score.score)
            print("item.score", sub_score.class_rank)
            print("item.score", sub_score.grade_rank)
            print("item.person.name",sub_score.person.name)"""
        cnt=cnt+1
    return(datalist)

def sav_or_get_data(userid,userpwd):
    student=login(userid,userpwd)
    exam = student.get_exams()
    save_user(student.name, student.clazz.school.name,student.clazz.name, userid, userpwd)
    scorelist=[]
    cnt=1
    for examl in exam:
        if cnt<11:#取最近10个考试
            examid = examl.id
            try:#如果数据库里有这个考试
                s = get_score_byname(student.name, examid)
                for item in s:
                    scorelist.append(item)

            except: #如果数据库没有
                try:#从智学网获取
                    create_table(examid)
                    print("新增表：",examid)
                    score=get_data_zhixue(examid)
                    print(dir(score))
                    insert_multi_data(score,examid)
                    s=get_score_byname(student.name,examid)
                    for item in s:
                        scorelist.append(item)
                except:#如果取不出来
                    pass
        cnt=cnt+1
    return scorelist